﻿Imports System.Data.SqlClient
Public Class dbReport
    Dim Var As New varDeclare
    Dim CompID As String = Var.CompID

    Public Function GetCompanyProfile() As DataTable
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT COMP_NAME [CompanyName], COMP_ADDRESS [CompanyAddress], COMP_CITY [CompanyCity], COMP_ZIPCODE [CompanyZIP], COMP_COUNTRY [CompanyCountry], COMP_TELP [CompanyPhone], COMP_FAX [CompanyFax], COMP_EMAIL [CompanyEmail] FROM SIS_COMPANY WITH(NOLOCK) WHERE COMP_CODE = '" & Var.CompID & "'"
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds.Tables(0)
    End Function

    Public Function GenerateJournalReport(ByVal JournalType As String, ByVal JournalYear As String, _
                                          ByVal JournalPeriod As String, ByVal JournalDocuStart As String, ByVal JournalDocuEnd As String) As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "proc_GenerateJournalReport"
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = CompID
        If (JournalType <> "") Then
            command.Parameters.Add("@JournalType", SqlDbType.VarChar).Value = JournalType
        End If
        If (JournalYear <> "") Then
            command.Parameters.Add("@JournalYear", SqlDbType.VarChar).Value = JournalYear
        End If
        If (JournalPeriod <> "") Then
            command.Parameters.Add("@JournalPeriod", SqlDbType.VarChar).Value = JournalPeriod
        End If
        If (JournalDocuStart <> "") Then
            command.Parameters.Add("@JournalDocuStart", SqlDbType.VarChar).Value = Convert.ToInt32(JournalDocuStart)
        End If
        If (JournalDocuEnd <> "") Then
            command.Parameters.Add("@JournalDocuEnd", SqlDbType.VarChar).Value = Convert.ToInt32(JournalDocuEnd)
        End If
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function
End Class
